"""
PlayerAware SQL generator

Maybe someday:
- Nice SQL formatting
- modular query construction .filter, [slice:slice], can this be done? (think outer join)
- Functional JOINS (don't actually know how to do them right now ... Setting up filters for one Source, this would be a bit tricky, since outer join condition takes precedence on where
- Precompilation of SQL queries
- Inserts, Updates and other commands beside SELECT
- Limiting by slicing (.query()[3:10], also .query().sql()) (needs cloning)
- Remove forein key hack (FK expression is placed under pk in the source when joining)
- A named attribute name can get overriden in a subquery in HAVING section (?) 

"""
from django.db import backend, transaction
from django.db import connection
from django.db import models
from django.db.models.base import Model
from timeit import itertools
from types import NoneType
from django.db.models.fields import Field
from django.db.models.base import ModelBase
from inspect import isclass
import datetime

######################################################################################
# Tools
######################################################################################
def quote_name(name):
    return backend.DatabaseOperations().quote_name(name) #@UndefinedVariable


def value(operand):
    """Make sure that operand is an Expression object"""
    if isinstance(operand, Expression):
        return operand
    elif isinstance(operand, Model):
        return PkExpression(operand)
    elif isiterable(operand) and not isinstance(operand, basestring):
        return RowExpression(operand)
    else:
        return ConstantValueExpression(operand)

def used_sources_from_expressions(iterable):
    sources = set()
    for expr in iterable:
        sources |= expr.used_sources()
    return sources

def make_class(cls, name, num_args):
    """Function used to make specific SQL expression types"""
    if num_args == 0:
        class Class(cls):
            def __init__(self): cls.__init__(self, name)
            def __repr__(self): return name + "()"
    elif num_args == 1:
        class Class(cls):
            def __init__(self, arg): cls.__init__(self, name, arg)
            def __repr__(self): return name + "(arg)"
    elif num_args == 2:
        class Class(cls):
            def __init__(self, arg1, arg2): cls.__init__(self, name, arg1, arg2)
            def __repr__(self): return name + "(arg1,arg2)"
    elif num_args == 4:
        class Class(cls):
            def __init__(self, arg1, arg2, arg3, arg4): cls.__init__(self, name, arg1, arg2, arg3, arg4)
            def __repr__(self): return name + "(arg1,arg2,arg3,arg4)"
    else:
        raise Exception("Unsupported number of arguments %s" % num_args)
    
    return Class

class ExpressionContext(dict):
    def __init__(self, var_num = 0):
        self.__names = set()
        self.__var_num = var_num
    
    def __getitem__(self, key):
        try:
            return dict.__getitem__(self, key)
        except KeyError:
            raise Exception("%s can not be used in this context (%s)" % (key, self))
        
    def __setitem__(self, key, value):
        #if value in self.__names:
        #    raise Exception("Name already taken %s" % value)
        #else:
        dict.__setitem__(self, key, value)
        #    self.__names.add(value)
    
    def next_var_num(self):
        self.__var_num += 1
        return self.__var_num
    
    def update(self, dict):
        for key, value in dict.iteritems():
            self[key] = value #make sure that __setitem__ is called
        
    def __add__(self, new_vars):
        ret = ExpressionContext(self.__var_num)
        ret.update(self)
        ret.update(new_vars)
        return ret

######################################################################################
# Expressions
######################################################################################
class Expression:
    def used_sources(self):
        """Retruns a set of sources used in the expression, this is used to generate FROM section"""
        return set()
    
    def do_render_expression(self, context):
        raise NotImplementedError()
    
    def render_expression(self, context):
        """Renders expression as a normal expression"""
        if (self in context):
            return context[self]
        else:
            return self.do_render_expression(context)
    
    def create_field(self, sql_name):
        return SimpleField(sql_name, self)
    
    def render_group_by(self, context):
        return self.render_expression(context)
    
    def touch_for_group_by(self):
        pass
    
    def get_model_field(self):
        return None
    
    def __hash__(self):
        return id(self)
    
    def __invert__(self): return PrefixExpression("NOT", self)
    def __add__(self, other): return InfixExpression("+", self, value(other))
    def __radd__(self, other): return InfixExpression("+", value(other), self)
    def __sub__(self, other): return InfixExpression("-", self, value(other))
    def __rsub__(self, other): return InfixExpression("-", value(other), self)
    def __mul__(self, other): return InfixExpression("*", self, value(other))
    def __rmul__(self, other): return InfixExpression("*", value(other), self)
    def __div__(self, other): return InfixExpression("/", self, value(other))
    def __rdiv__(self, other): return InfixExpression("/", value(other), self)
    def __pos__(self): return PrefixExpression("+", self)
    def __neg__(self): return PrefixExpression("-", self)
    def __pow__(self, other): raise NotImplementedError()
    def __rpow__(self, other): raise NotImplementedError()
    def __abs__(self): raise NotImplementedError()
    def __mod__(self, other): raise NotImplementedError()
    def __rmod__(self, other): raise NotImplementedError()
    def __lt__(self, other): return InfixExpression("<", self, value(other))
    def __le__(self, other): return InfixExpression("<=", self, value(other))
    def __gt__(self, other): return InfixExpression(">", self, value(other))
    def __ge__(self, other): return InfixExpression(">=", self, value(other))
    def __eq__(self, other): return InfixExpression("=", self, value(other))
    def __ne__(self, other): return InfixExpression("!=", self, value(other))
    def __and__(self, other): return InfixExpression("AND", self, value(other))
    def __rand__(self, other): return InfixExpression("AND", value(other), self)
    def __or__(self, other): return InfixExpression("OR", self, value(other))
    def __ror__(self, other): return InfixExpression("OR", value(other), self)

class ConstantValueExpression(Expression):
    def __init__(self, value):
        self.value = value
    
    def do_render_expression(self, context):
        if isinstance(self.value, basestring):
            return "'%s'" % self.value.replace("'", "''")
        elif isinstance(self.value, datetime.date):
            return "(DATE '%s')" % self.value
        elif isinstance(self.value, NoneType):
            return "NULL"
        else:
            return "%s" % self.value
        
    def __repr__(self):
        return "ConstantValueExpression(%s)" % self.value

class RowExpression(Expression):
    def __init__(self, args):
        self.args = [value(arg) for arg in args]
    
    def used_sources(self):
        return used_sources_from_expressions(self.args)

    def do_render_expression(self, context):
        return "(%s)" % (",".join(arg.render_expression(context) for arg in self.args), )

class TypeCastExpression(Expression):
    def __init__(self, type, v):
        self.type = type
        self.value = value(v)
    
    def used_sources(self):
        return self.value.used_sources()
    
    def do_render_expression(self, context):
        return "(%s)::%s" % (self.value.render_expression(context), self.type)

class VerbatimSQLExpression(Expression):
    def __init__(self, sql):
        self.sql = sql
    
    def do_render_expression(self, context):
        return self.sql
        
    def __repr__(self):
        return "VerbatimSQLExpression(%s)" % self.sql

class PkExpression(Expression):
    def __init__(self, instance):
        self.instance = instance
    
    def do_render_expression(self, context):
        return "%s" % self.instance.pk

class InfixExpression(Expression): 
    def __init__(self, operator, operand1, operand2):
        self.operator = operator
        self.operand1 = value(operand1)
        self.operand2 = value(operand2)

    def used_sources(self):
        return self.operand1.used_sources() | self.operand2.used_sources()  

    def do_render_expression(self, context):
        return "(%s %s %s)" % (self.operand1.render_expression(context),
                             self.operator,
                             self.operand2.render_expression(context))
        
    def __repr__(self):
        return "InfixExpression(%s, %s, %s)" % (self.operator, self.operand1, self.operand2)

class PrefixExpression(Expression):
    def __init__(self, operator, operand):
        self.operator = operator
        self.operand = value(operand)
    
    def used_sources(self):
        return self.operand.used_sources()
    
    def do_render_expression(self, context):
        return "(%s %s)" % (self.operator, self.operand.render_expression(context))

class SufixExpression(Expression):
    def __init__(self, operator, operand):
        self.operator = operator
        self.operand = value(operand)
    
    def used_sources(self):
        return self.operand.used_sources()
        
    def do_render_expression(self, context):
        return "%s %s" % (self.operand.render_expression(context), self.operator)

class FunctionCall(Expression):
    def __init__(self, function, *args):
        self.function = function
        self.args = [value(arg) for arg in args]
    
    def used_sources(self):
        return used_sources_from_expressions(self.args)

    def do_render_expression(self, context):
        return "%s(%s)" % (self.function, ",".join(arg.render_expression(context) for arg in self.args))

class IF(Expression):
    def __init__(self, when_expr, then_expr, else_expr = None):
        self.when_expr = value(when_expr)
        self.then_expr = value(then_expr)
        self.else_expr = value(else_expr)

    def used_sources(self):
        return self.when_expr.used_sources() | self.then_expr.used_sources() | self.else_expr.used_sources() 

    def do_render_expression(self, context):
        return "CASE WHEN %s THEN %s ELSE %s END" % (self.when_expr.render_expression(context),
                                                     self.then_expr.render_expression(context),
                                                     self.else_expr.render_expression(context))
        
    def __repr__(self):
        return "IF(%s, %s, %s)" % (self.when_expr, self.then_expr, self.else_expr)

######################################################################################
# Specific expressions
######################################################################################
MAX = make_class(FunctionCall, "MAX", 1)
MIN = make_class(FunctionCall, "MIN", 1)
COUNT = make_class(FunctionCall, "COUNT", 1)
SUM = make_class(FunctionCall, "SUM", 1)
AVG = make_class(FunctionCall, "AVG", 1)
POWER = make_class(FunctionCall, "POWER", 2)
LEAST = make_class(FunctionCall, "LEAST", 2)
GREATEST = make_class(FunctionCall, "GREATEST", 2)
IS_NULL = make_class(SufixExpression, "IS NULL", 1)
NOT = make_class(FunctionCall, "NOT", 1)  
DESC = make_class(SufixExpression, "DESC", 1)
SQRT = make_class(FunctionCall, "SQRT", 1)
NULLIF = make_class(FunctionCall, "NULLIF", 2)
COALESCE = make_class(FunctionCall, "COALESCE", 2)#IFNULL on MySQL
EXISTS = make_class(FunctionCall, "EXISTS", 1)
IN = make_class(InfixExpression, "IN", 2)
TRIM = make_class(FunctionCall, "TRIM", 1)
LOWER = make_class(FunctionCall, "LOWER", 1)
REGEXP_REPLACE = make_class(FunctionCall, "REGEXP_REPLACE", 4)
DATE_PART = make_class(FunctionCall, "DATE_PART", 2)
TO_CHAR = make_class(FunctionCall, "TO_CHAR", 2)

def IN(operand1, operand2):
    if not isinstance(operand2,Source) and \
       not isinstance(operand2,Expression) and \
       not len(operand2): # since there are no elements, we should be always false
        return False
    else:
        return InfixExpression("IN", operand1, operand2)

######################################################################################
# Symbol Access Expressions
######################################################################################
class SymbolAccess(Expression):
    """This class represents an outside access to a variable that is stored in a source"""
    
    def __init__(self, sql_name, source, model_field):
        self.sql_name = sql_name
        self.source = source
        self.model_field = model_field
        
    def get_model_field(self):
        return self.model_field
    
    def used_sources(self):
        return set([self.source])

    def render_expression(self, context):
        return "%s" % (context[self])
    
    def __repr__(self):
        return "SymbolAccess(%s, %s, %s, %s)\n" % (self.sql_name, self.source, self.model_field, id(self))
    
    def external_sql_vars(self, source, source_name):
        if id(self.source) == id(source): #Since those are Expressions normal == would just create a new expression 
            return {self : source_name + "." + quote_name(self.sql_name)}
        else:
            return {}
        
class SymbolAccessGroup(Expression):
    def __init__(self, model, source):
        self.__exprs = []
        
        if not (isinstance(model,ModelBase) or model is None):
            raise Exception("%s is not a Model" % model)
        
        self.__model = model
        self.__source = source
        
    def get_symbol_access(self, source, python_name):
        raise AttributeError()
    
    def __getattr__(self, python_name):
        if python_name.startswith("__"):
            raise AttributeError(python_name)
        
        symbol_access = self.get_symbol_access(self.__source, python_name)
        #if isinstance(symbol_access, FkSymbolAccess):
        #    self.__exprs.append(symbol_access.fk_expr)#So it will get a proper name
        #print ">", python_name, self.__model, symbol_access.__class__.__name__, "<"
        if symbol_access:
            setattr(self, python_name, symbol_access)
            self.__exprs.append(symbol_access)
            return symbol_access
        else:
            raise AttributeError(python_name)
    
    def get_model(self):
        return self.__model
    
    def get_model_field(self):
        return self.pk.get_model_field()
    
    def used_sources(self):
        return self.pk.used_sources()
    
    def render_expression(self, context):
        return self.pk.render_expression(context)
    
    def render_group_by(self, context):
        if self.__model:
            groups = [getattr(self, model_field.name) for model_field in self.__model._meta.fields]
            return ",".join(group.render_expression(context) for group in groups) + "\n"
        else:
            return self.render_expression(context)
    
    def touch_for_group_by(self):
        for model_field in self.__model._meta.fields:
            getattr(self, model_field.name)
    
    def create_field(self, sql_name):
        if self.__model:
            return ORMObjectField(sql_name, self.__model, self)
        else:
            # This will be used for selects, select can be threaded as an expression by itself
            return SimpleField(sql_name, self)

    def external_sql_vars(self, source, source_name):
        vars = {}
        
        try:
            self.pk #pk needs to be accessed so it will initialize properly
        except AttributeError:
            pass
        
        for expr in self.__exprs:
            vars.update(expr.external_sql_vars(source, source_name))
        
        return vars

class FkSymbolAccess(SymbolAccessGroup):
    def __init__(self, model, fk_expr):
        self.to = ModelSource(model)
        self.fk_expr = fk_expr
        
        #fk_expr must be a pk, so it will be taken into account in external_sql_vars
        SymbolAccessGroup.__init__(self, model, self.to)
        fk_expr.source.JOIN(self.to, fk_expr == self.to.pk)
    
    def get_symbol_access(self, source, python_name):
        """This is a bit hacked, normally get_symbol_access is only used by sources and fields,
           Here it's an access, hence source is always the same"""
        if id(self.to) != id(source):
            raise Exception("Sources should always be the same here!")
        if python_name == "pk":
            return self.fk_expr
        
        try:
            expr = getattr(self.to, python_name)
        
            if isinstance(expr, Expression): #We only want to symbol access
                return expr
        except AttributeError:
            pass

def create_symbol_access(sql_name, source, model_field):
    if model_field and isinstance(model_field, (models.ForeignKey, models.OneToOneField)):
        """Add a joined model source expression to exprs"""
        return FkSymbolAccess(model_field.rel.to, SymbolAccess(sql_name, source, model_field))
    else:
        return SymbolAccess(sql_name, source, model_field)

def get_model_symbol_access(model, source, python_name, field_sql_names = None):
    if not field_sql_names:
        field_sql_names = dict(itertools.izip(model._meta.fields, [model_field.attname for model_field in model._meta.fields]))
    
    if python_name == "pk":
        model_field = model._meta.pk
        return create_symbol_access(field_sql_names[model_field], source, model_field)
    
    for field in model._meta.many_to_many:
        if field.name == python_name:
            m2m_source = ManyToManySource(field, reverse_columns = False)
            to = ModelSource(field.rel.to)
            m2m_source.JOIN(to, to.pk == m2m_source.join_column)
            source.JOIN(m2m_source, m2m_source.intermediate_column == source.pk)
            return to
    

    for field in model._meta.get_all_related_many_to_many_objects():
        if field.get_accessor_name() == python_name:
            m2m_source = ManyToManySource(field.field, reverse_columns = True)
            to = ModelSource(field.model)
            m2m_source.JOIN(to, to.pk == m2m_source.join_column)
            source.JOIN(m2m_source, m2m_source.intermediate_column == source.pk)
            return to

    for related in model._meta.get_all_related_objects():
        if related.get_accessor_name() == python_name:
            to = ModelSource(related.model)
            source.JOIN(to, source.pk == getattr(to, related.field.name))
            return to
    
    
    for model_field in model._meta.fields:
        if model_field.name == python_name:
            return create_symbol_access(field_sql_names[model_field], source, model_field)

######################################################################################
# Fields
######################################################################################
class ORMObjectField:
    """This represents a subset of fields of a source, it's used to group model related vars"""
    
    def __init__(self, sql_name, model, symbol_access_group):
        self.__sql_name = sql_name
        self.__model = model
        self.__symbol_access_group = symbol_access_group
        
        #order expressions and remove garbage
        self.__exprs = self.__reorder_and_filter_exprs()
    
    def render_fields_as_from_expression(self, context):
        return ",".join("(%s) as %s" % (expr.render_expression(context), name)
                        for expr, name in self._iter_named_exprs())
        
    def get_internal_variables(self):
        """This is used to properly name GROUP_BY/HAVING/ORDER_BY statements"""
        return dict(self._iter_named_exprs())
    
    def get_value_from_row(self, row, start_idx):  
        kwargs = {}
        idx = 0
        for model_field in self.__symbol_access_group.get_model()._meta.fields:
            kwargs[model_field.attname] = row[start_idx + idx]                                
            idx += 1
            
        return self.__model(**kwargs), start_idx + len(kwargs)

    def used_sources(self):
        return used_sources_from_expressions(self.__exprs)
    
    def _iter_sql_names(self):
        return ("%sorm%d" % (self.__sql_name, idx) for idx in xrange(len(self.__exprs)))
    
    def _iter_named_exprs(self):
        return itertools.izip(self.__exprs, self._iter_sql_names())
    
    def __reorder_and_filter_exprs(self):
        return [getattr(self.__symbol_access_group, model_field.name) for model_field in self.__model._meta.fields]

    def get_symbol_access(self, source):
        model = self.__model
        field = self
        class ORMSag(SymbolAccessGroup):
            def __init__(self):
                SymbolAccessGroup.__init__(self, model, source)
                
            def get_symbol_access(self, source, python_name):
                return get_model_symbol_access(model, source, python_name,
                                               dict(itertools.izip(model._meta.fields, field._iter_sql_names())))
            
        return ORMSag()


class SimpleField:
    def __init__(self, sql_name, expr):
        """
            name - this is an identifier that will be used to reference this field from SQL
            expr - internal expression that is used to create this field (used only in selects)
        """
        self.__sql_name = sql_name
        self.__expr = value(expr)

    def render_fields_as_from_expression(self, context):
        return "(%s) as %s" % (self.__expr.render_expression(context), self.__sql_name)

    def used_sources(self):
        return self.__expr.used_sources()

    def get_internal_variables(self):
        """This is used to properly name GROUP_BY/HAVING/ORDER_BY statements"""
        return {self.__expr : self.__sql_name }
 
    def get_value_from_row(self, row, start_idx):
        return row[start_idx], start_idx + 1

    def get_symbol_access(self, source):
        return create_symbol_access(self.__sql_name, source, self.__expr.get_model_field())

######################################################################################
# Sources
######################################################################################
class Source(SymbolAccessGroup):
    def __init__(self, model):
        SymbolAccessGroup.__init__(self, model, self)
        self.__joined_to = None
    
    #joins modify the current source, that is bad, but it was required so the condition would refernce correct sources
    def JOIN(self, other_source, condtion): other_source.__join_to("JOIN", self, condtion)
    def INNER_JOIN(self, other_source, condtion): other_source.__join_to("INNER JOIN", self, condtion)
    def LEFT_JOIN(self, other_source, condtion): other_source.__join_to("LEFT JOIN", self, condtion)
    def LEFT_OUTER_JOIN(self, other_source, condtion): other_source.__join_to("LEFT OUTER JOIN", self, condtion)
    def RIGHT_JOIN(self, other_source, condtion): other_source.__join_to("RIGHT JOIN", self, condtion)
    def RIGHT_OUTER_JOIN(self, other_source, condtion): other_source.__join_to("RIGHT OUTER JOIN", self, condtion)
    def FULL_OUTER_JOIN(self, other_source, condtion): other_source.__join_to("FULL OUTER JOIN", self, condtion)
    def CROSS_JOIN(self, other_source, condtion): other_source.__join_to("CROSS JOIN", self, condtion)
    
    def __join_to(self, type, other_source, condition):
        if self.__joined_to:
            raise Exception("%s already is joined to %s"  % (self, self.__joined_to))
        else:
            self.__joined_to = (type, other_source, condition)
    
    def render_from(self, context):
        "Renders source as a FROM segment"
        raise NotImplementedError()
    
    '''
    def external_sql_vars(self, source, source_name):
        """Return a dictionary of expr->sql_var, expr must be in a context of the source, sql_vars must be created using this source
        this allows to recreate a structure of external_vars for UNION"""
        raise NotImplementedError()
    '''
    
    def render_from_with_joins(self, context, names, join_tree, join_type = None, join_condition = None):
        source_name = names.next()
        from_sql = self.render_from(context)
        
        external_vars = {}
        external_vars[self] = source_name
        external_vars.update(self.external_sql_vars(self, source_name))
        
        if join_type and join_condition:
            cond_sql = join_condition.render_expression(context + external_vars)
            
            sql = " " + join_type + " " + "%s as %s" % (from_sql, source_name) + \
                  " ON " + cond_sql
        else:
            sql = "%s as %s" % (from_sql, source_name)
        
        for join_type, source, condtion in join_tree[self]:
            src_sql, source_external_vars = source.render_from_with_joins(context + external_vars, names, join_tree, join_type, condtion)
            external_vars.update(source_external_vars)
            sql += src_sql
        
        return sql, external_vars    
    
    def fill_join_sources(self, sources):
        current = self
        
        while current not in sources:
            sources.add(current)
            if current.__joined_to:
                current = current.__joined_to[1]
    
    def get_joined_to(self):
        return self.__joined_to

class ModelSource(Source):
    """Model source, when used in expression context it is treated as a primary key"""
    
    def __init__(self, Model):
        Source.__init__(self, Model)
        
    def get_symbol_access(self, source, python_name):
        return get_model_symbol_access(self.get_model(), source, python_name)
    
    def render_from(self, context):
        return "%s" % (quote_name(self.get_model()._meta.db_table),)
    
    def __repr__(self):
        return "ModelSource(%s, %s)" % (id(self), self.get_model())

class ManyToManySource(Source):
    def __init__(self, m2mfield, reverse_columns = False):
        Source.__init__(self, None)
        self.m2mfield = m2mfield
        
        if reverse_columns:
            self.__intermediate_column = self.m2mfield.m2m_reverse_name()
            self.__join_column = self.m2mfield.m2m_column_name()
        else:
            self.__join_column = self.m2mfield.m2m_reverse_name()
            self.__intermediate_column = self.m2mfield.m2m_column_name()
    
    def get_symbol_access(self, source, python_name):
        if python_name == "id" or python_name == "pk":
            return SymbolAccess("id", source, None)
        elif python_name == "intermediate_column":
            return SymbolAccess(self.__intermediate_column, source, None)
        elif python_name == "join_column":
            return SymbolAccess(self.__join_column, source, None)
    
    def render_from(self, context):
        return "%s" % (quote_name(self.m2mfield.m2m_db_table()),)
    
    def __repr__(self):
        return "ManyToManySource(%s)" % self.get_model()

class SetOperation(Source):
    def __init__(self, selects, all, type):
        self.selects = selects #get_symbol_access must work during Source construction
        Source.__init__(self, selects[0].get_model())
        self.all = all
        self.type = type

        num_fields = len(self.selects[0].external_sql_vars(self.selects[0], 'dummy'))
        for select in self.selects:
            if len(select.external_sql_vars(select, 'dummy')) != num_fields:
                raise Exception("Incompatibile sqls")
    
    def get_symbol_access(self, source, python_name):
        return self.selects[0].get_symbol_access(source, python_name)
    
    '''
    def external_sql_vars(self, source, source_name):
        vars = {}
        vars[source] = source_name
        
        vars.update(self.selects[0].external_sql_vars(source, source_name))
        
        return vars
    '''
    
    def render_from(self, context):
        if self.all:
            all_string = "ALL "
        else:
            all_string = "" 
        
        sql = ""
        for i in xrange(0, len(self.selects)):
            select_sql = self.selects[i].render_from(context) #@UnusedVariable
            
            if i == 0:
                sql += select_sql
            else:
                sql += " %s %s%s" % (self.type, all_string, select_sql)
        
        return "(%s)" % (sql, )
    
def UNION(*selects): return SetOperation(selects, False, "UNION")
def UNION_ALL(*selects): return SetOperation(selects, True, "UNION")
def INTERSECT(*selects): return SetOperation(selects, False, "INTERSECT")
def INTERSECT_ALL(*selects): return SetOperation(selects, True, "INTERSECT")
def EXCEPT(*selects): return SetOperation(selects, False, "EXCEPT")
def EXCEPT_ALL(*selects): return SetOperation(selects, True, "EXCEPT")

def isiterable(x):
    return hasattr(x, "__iter__")

def should_be_iterable_expression(v):
    if not v:
        return []
    elif isiterable(v):
        return v
    else:
        return [value(v)]
        
class SELECT(Expression, Source):
    def __init__(self, fields, distinct = False, where = None, having = None, group_by = [], order_by = [], limit = None, offset = None):
        """
        Possible field types:
            - Pair (string, unnammed_field_spec) this is a named field spec.
            - SymbolAccessGroup with associated model, that means we have to treat it as an ORM Object
            - Other expressions
        """
        
        if isinstance(fields, dict):
            raise Exception("Dictionaries are no longer supported as a field list, use list of pairs instead")
        
        self._fields = []
        self._name_to_field = {}
        for field in should_be_iterable_expression(fields):
            if isiterable(field) and len(field) == 2:
                sql_name = field[0]
                expr = value(field[1])
            else:
                sql_name = "a%d" % len(self._fields)
                expr = field
                
            field = expr.create_field(sql_name)
            self._fields.append(expr.create_field(sql_name))
            self._name_to_field[sql_name] = field
        
        Source.__init__(self, None)
        
        
        self._group_by = should_be_iterable_expression(group_by)
        self._order_by = should_be_iterable_expression(order_by)
        
        def possibly_empty_value(expr):
            if isinstance(expr, NoneType):
                return None
            return value(expr)
        
        self._where = possibly_empty_value(where)
        self._having = possibly_empty_value(having)
        self._limit = possibly_empty_value(limit)
        self._offset = possibly_empty_value(offset)
        self._distinct = distinct

    def get_symbol_access(self, source, python_name):
        try:
            return self._name_to_field[python_name].get_symbol_access(source)
        except KeyError:
            pass
    
    def _get_sources_for_from(self):
        sources = set()
        sources |= used_sources_from_expressions(self._fields)
        if self._where:
            sources |= self._where.used_sources()
        if self._having:
            sources |= self._having.used_sources()
        sources |= used_sources_from_expressions(self._group_by)
        sources |= used_sources_from_expressions(self._order_by)
        return sources
    
    def render_from(self, context):
        sql = self.__render_select_stmt(context)
        return "(%s)" % (sql, )

    def get_sql(self):
        return self.__render_select_stmt(ExpressionContext())
    
    def __str__(self):
        return "SELECT"
    
    def query(self, cursor = None, params = ()):
        select = self
        class Iterable:
            
            def __get_retlist(self):
                if not hasattr(self, 'retlist'):
                    if cursor == None:
                        cur = connection.cursor() #@UndefinedVariable
                        cur.execute(select.get_sql(), params)
                        data = cur.fetchall()
                        cur.close()
                    else:
                        cursor.execute(select.get_sql(), params)
                        data = cursor.fetchall()
                    
                    self.retlist = []
                    for row in data:
                        """Map row data to field specifications and return"""
                        rowidx = 0
                        ret = []
                        for field in select._fields:
                            value, rowidx = field.get_value_from_row(row, rowidx)
                            ret.append(value)
                            
                        if len(ret) == 1:
                            self.retlist.append(ret[0])
                        else:
                            self.retlist.append(tuple(ret))
                return self.retlist
            
            def __getitem__(self, i):
                return self.__get_retlist()[i]
            
            def __len__(self):
                return self.__get_retlist().__len__()
            
            def __iter__(self):
                return self.__get_retlist().__iter__()
        
        return Iterable()

    def __render_select_stmt(self, context):
        for group in self._group_by:
            group.touch_for_group_by() #This is required so we will get all variables for group by exported
        
        internal_vars = {}
        for field in self._fields:
            internal_vars.update(field.get_internal_variables())
        
        sources = self._get_sources_for_from()
        
        for key in context.keys():
            sources.discard(key)
            
        from_sql, from_vars = self.__render_from(context, sources)
        fields_sql = ",".join(field.render_fields_as_from_expression(context + from_vars) for field in self._fields)
        
        
        if self._distinct:
            distinct_sql = "DISTINCT "
        else:
            distinct_sql = ""    
        
        sql = "SELECT " + distinct_sql + fields_sql + "\n"
        if sources:
            sql += "FROM " + from_sql + "\n"
        if self._where:
            sql += "WHERE " + self._where.render_expression(context + from_vars) + "\n"
        if self._group_by:
            sql += "GROUP BY " + ",".join(group.render_group_by(context + internal_vars + from_vars) for group in self._group_by) + "\n"
        if self._having:
            sql += "HAVING " + self._having.render_expression(context + internal_vars + from_vars) + "\n"
        if self._order_by:
            sql += "ORDER BY " + ",".join(order.render_expression(context + internal_vars + from_vars) for order in self._order_by) + "\n"
        if self._limit: 
            sql += "LIMIT " + self._limit.render_expression(context) + "\n"
        if self._offset: 
            sql += "OFFSET " + self._offset.render_expression(context)
            
        return sql
        
    def __render_from(self, context, sources):
        """Renders from section for this SELECT, also returns aliases"""
        
        def name_generator():
            while True:
                yield "src%d" % (context.next_var_num(), )
        names = name_generator()
        
        full_sources = set()    
        for source in sources:
            source.fill_join_sources(full_sources)
        
        join_tree = {}
        roots = []
        for source in full_sources: #all sources, even m2m and other hidden
            join_tree.setdefault(source, [])
            if source.get_joined_to():
                (type, other_source, condition) = source.get_joined_to()
                join_tree.setdefault(other_source, []).append((type, source, condition))
            else:
                roots.append(source) 
        
        sqls = []
        new_vars = {}        
        for source in roots:
            rendered_source, exported_vars = source.render_from_with_joins(context, names, join_tree)
            new_vars.update(exported_vars)
            sqls.append(rendered_source)
        
        sql = ",".join(sqls)    
        
        return sql, new_vars
    
    ### Expression ###
    def used_sources(self):
        return set()

    def render_expression(self, context):
        rendered = self.__render_select_stmt(context)
        return "(%s)" % rendered

def INSERT_INTO(target_model_or_db_table, list_of_fields_or_column_names = None, data = None):
    if hasattr(target_model_or_db_table, '_meta'):
        db_table = target_model_or_db_table._meta.db_table
    else:
        db_table = target_model_or_db_table
        
    list_of_column_names = []
    
    if not list_of_fields_or_column_names:
        list_of_fields_or_column_names = target_model_or_db_table._meta.fields
    
    for forc in list_of_fields_or_column_names:
        if isinstance(forc, Field):
            list_of_column_names.append(forc.attname)
        else:
            list_of_column_names.append(forc)
            
    if not data:
        raise Exception("No data supplied for insert")
    
    if isinstance(data, SELECT):
        data = "(%s)" % data.get_sql()
    else:
        data = "VALUES (%s)" % ", ".join(value(d).render_expression(ExpressionContext()) for d in data)
    
    sql = '''INSERT INTO %s (%s)
            %s''' % (quote_name(db_table),
                       ", ".join(quote_name(column) for column in list_of_column_names),
                       data
                       )

    class INSERT:
        def execute(self):
            cur = connection.cursor() #@UndefinedVariable
            cur.execute(sql)
            cur.close()
            transaction.commit_unless_managed()
        
        def get_sql(self):
            return sql
    
    return INSERT()
